﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using FFS.IDAL;
using FFS.Model;
using FFS.DBUtility;

namespace FFS.SQLDAL
{
    public class LoginUserDAO:ILoginUserDAO
    {
        private const string LOGIN_USER_ID = "@LoginUserID";
        private const string LOGIN_USER_NO = "@LoginUserNo";
        private const string LOGIN_USER_NAME = "@LoginUserName";
        private const string LOGIN_PASSWORD = "@LoginPassword";
        private const string LOGIN_IS_LOCKED = "@loginIsLocked";
        private const string INPUT_USER = "@input_user";

        private const string SELECT_ALL_LOGIN_USER = @" SELECT [LoginUserID]
                                                          ,[LoginUserNo]
                                                          ,[LoginUserName]
                                                          ,[LoginPassword]
                                                          ,[LoginIsLocked]
                                                          ,[input_user]
                                                          ,[input_date]
                                                      FROM [dbo].[LoginUser]
                                                      WHERE is_deleted = 0 ";
        private const string GET_NEW_LOGIN_USRE_NO = @"
                                                        DECLARE @NEW_LOGIN_USRE_NO nvarchar(50) = '1';
                                                        SELECT @NEW_LOGIN_USRE_NO = LTRIM(MAX(CAST(LoginUserNo as int)) + 1) 
                                                        FROM LoginUser 
                                                        SELECT @NEW_LOGIN_USRE_NO AS NEW_LOGIN_USER_NO 
                                                    ";

        private const string UPDATE_LOGIN_USER = @" UPDATE [dbo].[LoginUser]
                                                       SET [LoginUserName] = @LoginUserName 
                                                          ,[LoginPassword] = @LoginPassword 
                                                          ,[LoginIsLocked] = @LoginIsLocked 
                                                          ,[input_date] = getdate() 
                                                          ,[input_user] = @input_user 
                                                     WHERE is_deleted = 0 
                                                     and [LoginUserNo] = @LoginUserNo ";

        private const string UPDATE_LOGIN_USER_PASSWORD = @" UPDATE [dbo].[LoginUser]
                                                               SET [LoginPassword] = @LoginPassword 
                                                                  ,[input_date] = getdate() 
                                                                  ,[input_user] = @input_user 
                                                             WHERE is_deleted = 0 
                                                             and [LoginUserName] = @LoginUserName ";

        private const string INSERT_LOGIN_USER = @"  
                                                    INSERT INTO [dbo].[LoginUser]
                                                               ([LoginUserNo]
                                                               ,[LoginUserName]
                                                               ,[LoginPassword]
                                                               ,[LoginIsLocked]
                                                               ,[is_deleted]
                                                               ,[input_date]
                                                               ,[input_user])
                                                         VALUES
                                                               (@LoginUserNo 
                                                               ,@LoginUserName 
                                                               ,@LoginPassword 
                                                               ,@LoginIsLocked 
                                                               ,0  
                                                               ,GETDATE()  
                                                               ,@input_user ) 


                                                  ";
        private const string DELETE_LOGIN_USER = @" UPDATE loginUser 
                                                    set is_deleted = 1,input_date = getdate() ,input_User = @input_user 
                                                    where is_deleted = 0 and LoginUserNo = @LoginUserNo";
        private const string IS_LOCKED_LOGIN_USER = @" UPDATE loginUser 
                                                        set LoginIsLocked = @loginIsLocked,input_date = getdate() ,input_User = @input_user 
                                                        where is_deleted = 0 
                                                          and LoginUserNo = @LoginUserNo";


        private string GetNewLoginUserNO()
        {
            string newLoginUserNo = "1";
            object obj = DBHelper.ExecuteScalar(CommandType.Text, GET_NEW_LOGIN_USRE_NO, null);
            newLoginUserNo = obj.ToString().Trim(); 

            return newLoginUserNo;
        }

        private List<LoginUserInfo> GetQueryList(CommandType cmdType,string sqlText,params SqlParameter[] param)
        {
            List<LoginUserInfo> loginUserList = new List<LoginUserInfo>();
            using (SqlDataReader reader = DBHelper.ExecuteReader(cmdType,sqlText,param))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        LoginUserInfo user = new LoginUserInfo();
                        user.LoginUserID = reader.GetInt32(0);
                        user.LoginUserNo = reader.GetString(1);
                        user.LoginUserName = reader.GetString(2);
                        user.LoginPassword = reader.GetString(3);
                        user.LoginIsLocked = reader.GetBoolean(4);
                        user.Input_User = reader.GetString(5);
                        user.Input_Date = reader.GetDateTime(6);
                        loginUserList.Add(user);
                    }
                }
            }

            return loginUserList;
        }


        #region ILoginUserDAO 成员

        public List<LoginUserInfo> GetAllLoginUser()
        {
            return GetQueryList(CommandType.Text,SELECT_ALL_LOGIN_USER,null);
        }

        public List<LoginUserInfo> GetLoginUserByLoginNo(string loginUserNo)
        {
            string sqlText = SELECT_ALL_LOGIN_USER + " and LoginUserNo = @LoginUserNo" ;
            SqlParameter[] param = {
                                        new SqlParameter(LOGIN_USER_ID,loginUserNo)
                                   };
            return GetQueryList(CommandType.Text,sqlText,param);
        }

        public List<LoginUserInfo> GetLoginUserByLoginName(string loginUserName)
        {
            string sqlText = SELECT_ALL_LOGIN_USER + " and LoginUserName = @LoginUserName";
            SqlParameter[] param = {
                                        new SqlParameter(LOGIN_USER_NAME,loginUserName)
                                   };
            return GetQueryList(CommandType.Text, sqlText, param);
        }

        public List<LoginUserInfo> GetLoginUserBySearchCondition(string searchCondition)
        {
            string sqlText = SELECT_ALL_LOGIN_USER + " and " + searchCondition;
            return GetQueryList(CommandType.Text, sqlText, null);
        }

        public List<LoginUserInfo> GetLoginUserByIsLocked(bool loginIsLocked)
        {
            string sqlText = SELECT_ALL_LOGIN_USER + " and LoginIsLocked = @LoginIsLocked ";
            SqlParameter param = new SqlParameter(LOGIN_IS_LOCKED,loginIsLocked);
            return GetQueryList(CommandType.Text,sqlText,param);
        }

        public int CreateLoginUser(LoginUserInfo sluf)
        {
            sluf.LoginUserNo = this.GetNewLoginUserNO();
            SqlParameter[] param = {
                                        new SqlParameter(LOGIN_USER_NO,sluf.LoginUserNo),
                                        new SqlParameter(LOGIN_USER_NAME,sluf.LoginUserName),
                                        new SqlParameter(LOGIN_PASSWORD,sluf.LoginPassword),
                                        new SqlParameter(LOGIN_IS_LOCKED,sluf.LoginIsLocked),
                                        new SqlParameter(INPUT_USER,sluf.Input_User)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text,INSERT_LOGIN_USER,param);
        }

        public int ModifyLoginUser(LoginUserInfo sluf)
        {
            SqlParameter[] param = {
                                        new SqlParameter(LOGIN_USER_NO,sluf.LoginUserNo),
                                        new SqlParameter(LOGIN_USER_NAME,sluf.LoginUserName),
                                        new SqlParameter(LOGIN_PASSWORD,sluf.LoginPassword),
                                        new SqlParameter(LOGIN_IS_LOCKED,sluf.LoginIsLocked),
                                        new SqlParameter(INPUT_USER,sluf.Input_User)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text,  UPDATE_LOGIN_USER, param);
        }

        public int DeleteLoginUser(LoginUserInfo sluf)
        {
            SqlParameter[] param = {
                                        new SqlParameter(LOGIN_USER_NO,sluf.LoginUserNo),
                                        new SqlParameter(INPUT_USER,sluf.Input_User)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text, DELETE_LOGIN_USER, param);
        }

        public bool IsExistsLoginUser(string loginUserNo)
        {
            SqlParameter[] param = {
                                        new SqlParameter(LOGIN_USER_NO,loginUserNo)
                                   };
            if (DBHelper.ExecuteScalar(CommandType.Text, SELECT_ALL_LOGIN_USER + " and LoginUserNo = @LoginUserNo ", param) != null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int IsLockedLoginUser(string loginUserNo, bool IsLocked,string input_user)
        {
            SqlParameter[] param = {
                                        new SqlParameter(LOGIN_USER_NO,loginUserNo),
                                        new SqlParameter(LOGIN_IS_LOCKED,IsLocked),
                                        new SqlParameter(INPUT_USER,input_user)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text, IS_LOCKED_LOGIN_USER, param);
        }

        public bool VerifyLogin(string userName, string password)
        { 
            string sqlText = SELECT_ALL_LOGIN_USER + " and LoginUserName = @LoginUserName and LoginPassword  = @LoginPassword ";
            SqlParameter[] param = {
                                        new SqlParameter(LOGIN_USER_NAME,SqlDbType.NVarChar,100),
                                        new SqlParameter(LOGIN_PASSWORD,SqlDbType.NVarChar,100)
                                   };
            param[0].Value = userName;
            param[1].Value = password;
            if (DBHelper.ExecuteScalar(CommandType.Text, sqlText, param) == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public int ModifyLoginUserPassword(LoginUserInfo user)
        {
            SqlParameter[] param = {
                                        new SqlParameter(LOGIN_USER_NAME,user.LoginUserName),
                                        new SqlParameter(LOGIN_PASSWORD,user.LoginPassword),
                                        new SqlParameter(INPUT_USER,user.Input_User)
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text, UPDATE_LOGIN_USER_PASSWORD, param);
        }

        #endregion
    }
}
